package com.teach.dao.impl;

import com.teach.dto.CountDto;
import com.teach.entity.CountryInfo;
import com.teach.dao.CountryInfoDAO;
import com.teach.util.DBUtil;
import com.teach.util.DBUtil.BaseDAOImpl;

import java.sql.SQLException;
import java.util.List;

public class CountryInfoDAOImpl extends BaseDAOImpl<CountryInfo> implements CountryInfoDAO {

  @Override
  public List<CountDto> countMedal() throws SQLException {
    String sql = "SELECT\n" +
        " tb1.*,\n" +
        " IFNULL(tb2.num1,0) as num1,\n" +
        " IFNULL(tb2.num2,0) as num2,\n" +
        " IFNULL(tb2.num3,0) as num3,\n" +
        " IFNULL(tb2.total,0) as total\n" +
        "FROM country_info tb1\n" +
        "LEFT JOIN\n" +
        "(\n" +
        "\tSELECT \n" +
        "\t\tcountry_code,\n" +
        "\t\tcount(if(medal_num=1,1,null)) as num1,\n" +
        "\t\tcount(if(medal_num=2,1,null)) as num2,\n" +
        "\t\tcount(if(medal_num=3,1,null)) as num3,\n" +
        "\t\tcount(*) as total\n" +
        "\tfrom (\n" +
        "\tSELECT DISTINCT b.country_code,se_id,a.medal_num from medal_info a\n" +
        "\tINNER JOIN sportsman_info b\n" +
        "\tON a.sportsman_id=b.sportsman_id\n" +
        "\t)c GROUP BY country_code\n" +
        ") tb2\n" +
        "ON tb1.country_code=tb2.country_code order BY num1 desc, num2 desc,num3 desc";
    return DBUtil.selectList(sql,CountDto.class);
  }
}
